package com.edu.dao;

import com.edu.bean.CampusStyle;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CampusStyleDao {

    /**
     * 查询所有校园风采
     */
    public List<CampusStyle> findAll() {
        String sql = "SELECT * FROM campus_style ORDER BY style_id DESC";
        List<CampusStyle> styleList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                styleList.add(convertToCampusStyle(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return styleList;
    }

    /**
     * 根据ID查询校园风采
     */
    public CampusStyle findById(Integer styleId) {
        String sql = "SELECT * FROM campus_style WHERE style_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, styleId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToCampusStyle(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加校园风采
     */
    public boolean add(CampusStyle style) {
        String sql = "INSERT INTO campus_style (title, description, image_url, status, create_time) " +
                "VALUES (?, ?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, style.getTitle());
            stmt.setString(2, style.getDescription());
            stmt.setString(3, style.getImageUrl());
            stmt.setInt(4, style.getStatus());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新校园风采
     */
    public boolean update(CampusStyle style) {
        String sql = "UPDATE campus_style SET title = ?, description = ?, status = ?, update_time = NOW()";
        if (style.getImageUrl() != null) {
            sql += ", image_url = ?";
        }
        sql += " WHERE style_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, style.getTitle());
            stmt.setString(2, style.getDescription());
            stmt.setInt(3, style.getStatus());

            if (style.getImageUrl() != null) {
                stmt.setString(4, style.getImageUrl());
                stmt.setInt(5, style.getStyleId());
            } else {
                stmt.setInt(4, style.getStyleId());
            }

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除校园风采
     */
    public boolean delete(Integer styleId) {
        String sql = "DELETE FROM campus_style WHERE style_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, styleId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String title) {
        String sql = "SELECT COUNT(*) FROM campus_style";
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql += " WHERE title LIKE ?";
            params.add("%" + title.trim() + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 分页查询校园风采
     */
    public List<CampusStyle> findByPage(String title, int start, int pageSize) {
        String sql = "SELECT * FROM campus_style";
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql += " WHERE title LIKE ?";
            params.add("%" + title.trim() + "%");
        }

        sql += " ORDER BY style_id DESC LIMIT ?, ?";
        params.add(start);
        params.add(pageSize);

        List<CampusStyle> styleList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                styleList.add(convertToCampusStyle(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return styleList;
    }

    /**
     * 随机获取指定数量的校园风采
     */
    public List<CampusStyle> findRandomStyles(int limit) {
        String sql = "SELECT * FROM campus_style WHERE status = 1 ORDER BY RAND() LIMIT ?";
        List<CampusStyle> styleList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                styleList.add(convertToCampusStyle(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return styleList;
    }

    /**
     * 将ResultSet转换为CampusStyle对象
     */
    private CampusStyle convertToCampusStyle(ResultSet rs) throws SQLException {
        CampusStyle style = new CampusStyle();
        style.setStyleId(rs.getInt("style_id"));
        style.setTitle(rs.getString("title"));
        style.setDescription(rs.getString("description"));
        style.setImageUrl(rs.getString("image_url"));
        style.setStatus(rs.getInt("status"));
        style.setCreateTime(rs.getTimestamp("create_time"));
        style.setUpdateTime(rs.getTimestamp("update_time"));
        return style;
    }
}